
# Import all necessary libraries
import sqlite3
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
from PIL import Image
import pandas as pd
import folium
import matplotlib.pyplot as plt
from IPython.display import display


# Display muninch_map
image = Image.open('output/project-flow.png')
display(image)

#Data Extraction
def fetch_kaggle_dataset(dataset, target_folder, filename):
    api = KaggleApi()
    api.authenticate()
    api.authenticate()
    username, dataset_name = dataset.split('/')[-2:]
    zip_file_path = os.path.join(target_folder,f"{dataset_name}.zip")
    api.dataset_download_files(f"{username}/{dataset_name}", path=target_folder, unzip=False)
    with zipfile.ZipFile(zip_file_path, 'r') as zip_ref:
        zip_ref.extract(filename, path=target_folder)

def data_extraction_csv(path):
    t1 = time()
    print("Data Extraction in progress...")

    try:
        df = pd.read_csv(path, encoding='latin-1')
    except Exception as e:
        print("Error occurred during file reading:", str(e))
        return None
    t2 = time()
    print("Finish: Data Extraction {} s ".format(t2 - t1))
    return df

    def data_extraction_xls(path):
    t1 = time()
    print("Data Extraction in progress...")

    try:
        df = pd.read_excel(path, engine='openpyxl')
    except Exception as e:
        print("Error occurred during file reading:", str(e))
        return None
    t2 = time()
    print("Finish: Data Extraction {} s ".format(t2 - t1))
    return df

# Check if column is empty and drop corresponding rows
    df1.dropna(subset=['regio1', 'regio2', 'noRooms'], inplace=True)

    # Fetch only Bayren state dataset
    df1 = df1[df1['regio1'] == 'Bayern']

    # correct the formats of data values
    df1['regio2'] = df1['regio2'].str.encode('latin-1').str.decode('utf-8')

    # only fetch data with city name available in students data
    university_cities = df2['City'].unique()
    df1 = df1[df1['regio2'].isin(university_cities)]

    # Format dataset
    df1 = data_transformation(df1, df1_rename_cols, df1_drop_cols)
    data_loader("../dataset.sqlite", df1, "immoscout")

     # correct date format in year cloumn
    df2['Jahr'] = pd.to_datetime(df2['Jahr'].str.split('/').str[0], format='%Y')
    df2['Jahr'] = df2['Jahr'].dt.strftime('%Y')

    #drop rows with num values
    df2 = df2.dropna()

    # List of columns to convert from float64 to int
    columns = ['Geisteswissenschaften', 'Sozialwissenschaften', 'Mathematik',
                 'Ingenieurwissenschaften', 'Informatik',
                 'medizin', 'Landwirtschaft']

    # Convert selected columns to int
    df2[columns] = df2[columns].astype(int)

# Query the database and load all data into a DataFrame
immoscout_table = "SELECT * FROM immoscout"
immoscout_table = pd.read_sql_query(immoscout_table, conn)

# Create a geocoder instance
geolocator = Nominatim(user_agent="my_app")

# Initialize lists to store latitude and longitude values
latitudes = []
longitudes = []

# Iterate over each row in the DataFrame
for index, row in immoscout_table.iterrows():
    # Check if the city is Munich or Nuremberg
    if row['City'] in ['München', 'Nürnberg']:
        address = f"{row['houseNumber']} {row['street']}, {row['Town']}, {row['City']}, {row['zipCode']} , {row['federalState']}"
        try:
            location = geolocator.geocode(address, timeout=10)
            if location is not None:
                latitudes.append(location.latitude)
                longitudes.append(location.longitude)
                print(address, ":", location.latitude, location.longitude)
            else:
                latitudes.append(None)
                longitudes.append(None)
        except GeocoderTimedOut:
            print(f"Geocoding timed out for address: {address}")
    else:
        # For other cities, use the existing latitude and longitude values
        latitudes.append(None)
        longitudes.append(None)

# Add latitude and longitude columns to the DataFrame
immoscout_table['latitude'] = latitudes
immoscout_table['longitude'] = longitudes

# Save the updated DataFrame back to the database
immoscout_table.to_sql('immoscout', conn, if_exists='replace', index=False)


# Connect to the database
conn = sqlite3.connect('../dataset.sqlite')  

# Query the database and load data into a DataFrames
immoscout_table = f"SELECT * FROM immoscout"
immoscout_df = pd.read_sql_query(immoscout_table, conn)
immoscout_df.head()


# Connect to the database
conn = sqlite3.connect('../dataset.sqlite')  

# Query the database and load forigen students data into a DataFrames
intstudents_table = f"SELECT * FROM intstudents"
intstudents_df = pd.read_sql_query(intstudents_table, conn)
intstudents_df.head()


# List of columns to convert from float64 to int
columns = ['Humanities', 'Social sciences', 'Mathematics',
                 'Engineering sciences', 'Computer Science',
                 'Medicine', 'Agriculture']

# Calculate total students and add a new column
intstudents_df['Total-Student'] = intstudents_df[columns].sum(axis=1)

columns_to_use = ['City', 'Total-Student']
df_bar = intstudents_df[columns_to_use].groupby('City').sum()

# Sort the DataFrame by Total-Student in ascending order
df_bar = df_bar.sort_values(by='Total-Student', ascending=False)

# Set a seaborn color palette for different cities
city_palette = sns.color_palette("husl", n_colors=len(df_bar))

# Plot the compound bar graph using seaborn
fig, ax = plt.subplots(figsize=(10, 6))
sns.barplot(x=df_bar.index, y='Total-Student', data=df_bar, palette=city_palette, hue=df_bar.index)

ax.set_xlabel('Cities')
ax.set_ylabel('No of Students')
ax.set_title('Distribution of Forigen Students in Cities of Bavaria')

# Rotate x-axis labels for better visibility
plt.xticks(rotation=45, ha='right')

# Remove the legend, as it's not needed in this case
ax.legend().set_visible(False)

plt.show()

No artists with labels found to put in legend.  Note that artists whose label start with an underscore are ignored when legend() is called with no argument.


columns_to_use = ['City', 'Year', 'Total-Student']
df_trendline = intstudents_df[columns_to_use].copy()
df_trendline = df_trendline[df_trendline['Year'] > '1998']
# Group by 'City' and 'Year', and sum the 'Total-Student' values within each group
df_trendline_grouped = df_trendline.groupby(['City', 'Year'])['Total-Student'].sum().reset_index()

# Sort the DataFrame by 'Year'
df_trendline_grouped = df_trendline_grouped.sort_values(by='Year')

sns.set(style="darkgrid")

# Create a trendline plot
plt.figure(figsize=(12, 8))
ax = sns.lineplot(x='Year', y='Total-Student', hue='City', data=df_trendline_grouped, errorbar=None, marker='o', markersize=8)

plt.title('Trendline Plot of Forigen Students Over 20 Years')
plt.xlabel('Year')
plt.ylabel('Total Students')
plt.legend(title='City', loc='upper left', bbox_to_anchor=(1, 1))

# Rotate x-axis labels for better visibility
plt.xticks(rotation=45, ha='right')

plt.show()


apartments_wrt_town = immoscout_df['City'].value_counts()

# Sort the values for better visualization
apartments_wrt_town_sorted = apartments_wrt_town.sort_values(ascending=False)

# Set a seaborn color palette for different cities
town_palette = sns.color_palette("tab10", n_colors=len(apartments_wrt_town_sorted))

# Plot the pie chart using seaborn
plt.figure(figsize=(8, 8))
plt.pie(apartments_wrt_town_sorted.values, labels=apartments_wrt_town_sorted.index, autopct='%1.1f%%', colors=town_palette)
plt.title('Percentage of apartments based on cities in Bavaria')
plt.axis('equal') 
plt.show()


# Extract data for Munich and Nuremberg
munich_data = immoscout_df[immoscout_df['City'] == 'München'][['latitude', 'longitude']].dropna().values.tolist()
nuremberg_data = immoscout_df[immoscout_df['City'] == 'Nürnberg'][['latitude', 'longitude']].dropna().values.tolist()


# Create a Folium map
map_center = [48.1351, 11.5820]  # Center the map around Munich
munich_map = folium.Map(location=map_center, zoom_start=11)

# Add Circle markers to the map
for location in munich_data:
    folium.Circle(location=location, radius=200, color='blue', fill=True).add_to(munich_map)

# Display muninch_map
image = Image.open('output/munich_map.PNG')
display(image)


# Create a Folium map
map_center = [49.4521, 11.0767]  # Center the map around Munich
nurnberg_map = folium.Map(location=map_center, zoom_start=11)

# Add Circle markers to the map
for location in nuremberg_data:
    folium.Circle(location=location, radius=200, color='blue', fill=True).add_to(nurnberg_map)

# Display nurnberg_map
image = Image.open('output/nurnberg_map.PNG')
display(image)


# Filter the DataFrame to include only rows where 'noRooms' is an integer
filtered_df = immoscout_df[immoscout_df['noRooms'].astype(int) == immoscout_df['noRooms']]

# Group by Cities and calculate the total number of rooms in each city
total_rooms_per_city = filtered_df.groupby('City')['noRooms'].sum().reset_index()


columns_to_use = ['City', 'Total-Student']
# Fetch student data for year 2022
intstudents_df = intstudents_df[intstudents_df['Year'] == '2022']

#groupby data according to Cities
df_bar = intstudents_df[columns_to_use].groupby('City').sum()
df_bar.reset_index(inplace=True)
df_bar.rename(columns={'City': 'City'}, inplace=True)


# Claulate percatage of number of room occupied and available in cities in 2022
df_bar['Total-Student-percentage'] = (df_bar['Total-Student']/total_rooms_per_city['noRooms'])*100


# Set the Seaborn style to 'whitegrid' for a light background
sns.set_style("whitegrid")

# Create a bar plot with visually attractive color scheme
plt.figure(figsize=(10, 6), facecolor='None')  

# Define visually attractive colors
total_students_color = sns.color_palette("Blues")[4] 
no_rooms_color = sns.color_palette("Oranges")[4]  

ax1 = sns.barplot(x='City', y='noRooms', data=total_rooms_per_city, color=no_rooms_color, edgecolor='black', label='No Rooms')
ax2 = sns.barplot(x='City', y='Total-Student', data=df_bar, color=total_students_color, edgecolor='black', label='Total Students')

# Annotate each bar with values from the 'annotation_column'
for p, annotation_value in zip(ax1.patches, df_bar['Total-Student-percentage']):
    ax1.annotate(f'{annotation_value:.0f}%', (p.get_x() + p.get_width() / 2., p.get_height()),
                ha='center', va='center', xytext=(0, 10), textcoords='offset points')

# Set labels and title
plt.xlabel('City')
plt.ylabel('Student Count')
plt.title('Total Students and No Rooms by Cities')

# Rotate x-axis labels for better visibility
plt.xticks(rotation=45, ha='right')
# Display the legend
plt.legend()
plt.ylim(0,11000)
# Show the plot
plt.show()

	federalState	serviceCharge	heatingType	totalRent	yearConstructed	noParkSpaces	hasKitchen	cellar	baseRent	houseNumber	...	noRoomsRange	livingSpaceRange	City	Town	description	facilities	latitude	longitude
0	Bayern	180.0	central_heating	2030.0	1871.0	0.0	1	1	1850.0	0	...	2	4	München	Altstadt	Die Wohnung (OHNE Balkon !!) liegt im 4. OG (O...	- Eichenparkettboden im Wohn-EZ u. im Schlafzi...	NaN	NaN
1	Bayern	0.0	0	2400.0	1956.0	1.0	0	0	2400.0	3	...	3	3	München	Schwabing_West	Highlights:\n\n * alle WÃ¤nde der WohnrÃ¤ume, ...	Waschmaschine in der Wohnung\nFlachbildfernseh...	NaN	NaN
2	Bayern	170.0	floor_heating	970.0	1974.0	1.0	1	1	800.0	0	...	2	5	Passau	Haidenhof_SÃ¼d	WOHNEN UND ENTSPANNEN!\nÂ \nDie Architektur be...	- Gartenwohnung in bester Lage\n- 2 Zimmer + H...	NaN	NaN
3	Bayern	0.0	0	2780.0	2016.0	0.0	0	0	2780.0	51	...	3	4	München	Milbertshofen	Bitte beachten Sie: Die Wohnung ist komplett m...	Waschmaschine in der Wohnung\nFlachbildfernseh...	48.18808	11.54337
4	Bayern	200.0	gas_heating	0.0	0.0	0.0	1	0	900.0	0	...	4	4	Würzburg	Lengfeld	Helle 4,5-Zimmerwohnung in ruhigem, kleinen Wo...	0	NaN	NaN

	Region	University	City	Year	Humanities	Social sciences	Mathematics	Engineering sciences	Computer Science	Medicine	Agriculture
0	bayren	Ostbayerische Technische Hochschule (OTH) Rege...	Regensburg	1998	30	16	58	4	74	18	16
1	bayren	Ostbayerische Technische Hochschule (OTH) Rege...	Regensburg	1999	20	20	58	0	36	20	20
2	bayren	Ostbayerische Technische Hochschule (OTH) Rege...	Regensburg	2000	27	18	58	2	31	18	18
3	bayren	Ostbayerische Technische Hochschule (OTH) Rege...	Regensburg	2001	27	18	58	4	36	18	18
4	bayren	Ostbayerische Technische Hochschule (OTH) Rege...	Regensburg	2002	42	18	58	4	42	18	18

Project Report¶

Navigating Germany's Rental Landscape for International Students¶

Summary¶

Rationale¶

Project Flow¶

Used Libraries¶

Data Extraction¶

Data Cleaning and Preprocessing:¶

Extract Latitude and Longitude for Immoscout24 Dataset¶

Database Structure¶

Expolatory Data Analysis¶

Bar Plot¶

Bar Plot Insight¶

Trendline Plot¶

Trenline Plot Insight¶

Pie Plot¶

Pie Plot Insight¶

Bubble Plot¶

Bubble Plot Insight¶

Comparision Analysis¶

Filled-In Bar Plot¶

Filled-In Bar Plot Insight¶

Conclusion¶